package com.cheers.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.cheers.dao.LuckEventDao;
import com.cheers.model.LuckEvent;
import com.cheers.mvo.LuckEventJoinUser;
import com.cheers.mvo.LuckEventLog;
import com.cheers.mvo.MyCode;
import com.cheers.mvo.Winner;

public class LuckEventDaoImpl extends AbstractDaoImpl implements LuckEventDao {
	
	@Override
	public List<String> getEventJoinUser(String eventId, String winnerId)
			throws Exception {
		String sql = "SELECT userId FROM luckeventcode_info where luckId=? and userId<>? GROUP BY userId";
		List<String> list = findBySql(sql, new Object[]{eventId, winnerId});
		return list;
	}

	@Override
	public List<LuckEvent> recd() throws Exception {
		String sql = "select * from luckevent_info where seq<>0 and status<>2 order by seq";
		List<LuckEvent> list = findBySql(LuckEvent.class, 0, 100, sql, null);
		return list;
	}

	@Override
	public LuckEvent load(String cgyId, int phase) throws Exception {
		LuckEvent result = null;
		String sql = "select * from luckevent_info where cgyId=? and phase=?";
		Object[] param = new Object[]{cgyId, phase};
		List<LuckEvent> list = findBySql(LuckEvent.class, 0, 10, sql, param);
		if (list!=null && !list.isEmpty()) {
			result = list.get(0);
		}
		return result;
	}

	@Override
	public Object[] getNear(String luckId, int code, int type) throws Exception {
		Object[] result = null;
		String operate = null;
		switch (type) {
			case 1:
				operate = "=";
				break;
			case 2:
				operate = "<";
				break;
			case 3:
				operate = ">";
				break;
			default:
				operate = "=";
		}
		String sql = "select lec.userId,lec.luckCode from luckeventcode_info lec where lec.luckCode"+operate+"? limit 1";
		Integer[] param = new Integer[]{code};
		List<Object[]> list = findBySql(sql, param);
		if (list!=null && !list.isEmpty()) {
			result = list.get(0);
		}
		return result;
	}

	@Override
	public List<Date> listExchangeLog() throws Exception {
		String sql = "select elk.ctime from exchangeluckevent_log elk order by elk.ctime desc limit 100";
		List<Date> result = findBySql(sql, null);
		return result;
	}

	@Override
	public List<Date> listLuckEvent() throws Exception {
		String sql = "select lk.ctime from luckevent_info lk order by lk.ctime desc limit 100";
		List<Date> result = findBySql(sql, null);
		return result;
	}

	@Override
	public Winner winner(String luckId) throws Exception {
		Winner result = null;
		String sql = "select u.id,u.username, le.id,le.title,le.picture, l.winCode,l.ctime from lottery_info l, user_info u, luckevent_info le where l.luckId=? and l.status=1 and l.winnerId=u.id and l.luckId=le.id order by l.ctime desc";
		Object[] param = new Object[]{luckId};
		List<Object[]> list = findBySql(sql, param);
		if (list!=null && !list.isEmpty()) {
			Object[] obj = list.get(0);
			result = new Winner();
			result.setUserId(String.valueOf(obj[0]));
			result.setUsername(String.valueOf(obj[1]));
			result.setLuckId(String.valueOf(obj[2]));
			result.setTitle(String.valueOf(obj[3]));
			result.setTitle(String.valueOf(obj[4]));
			result.setCode(String.valueOf(obj[5]));
			result.setCtime(sdf.format(obj[6]));
		}
		return result;
	}

	@Override
	public List<MyCode> codeList(String userId, int firstIndex, int pageSize)
			throws Exception {
		List<MyCode> result = null;
		String sql = "select le.id,le.title,le.price,le.picture, lec.luckCode,lec.ctime from luckeventcode_info lec, luckevent_info le where lec.userId=? and lec.luckId=le.id order by lec.ctime desc";
		Object[] param = new Object[]{userId};
		List<Object[]> list = findBySql(firstIndex, pageSize, sql, param);
		if (list!=null && !list.isEmpty()) {
			result = new ArrayList<MyCode>();
			MyCode code = null;
			for (Object[] obj : list) {
				code = new MyCode();
				code.setLuckId(String.valueOf(obj[0]));
				code.setTitle(String.valueOf(obj[1]));
				code.setPrice(String.valueOf(obj[2]));
				code.setPicture(String.valueOf(obj[3]));
				code.setCode(String.valueOf(obj[4]));
				code.setCtime(sdf.format(obj[5]));
				
				result.add(code);
			}
		}
		return result;
	}

	@Override
	public List<Winner> winnerList(int firstIndex, int pageSize)
			throws Exception {
		List<Winner> result = null;
		String sql = "select u.id,u.username, le.id,le.title,le.picture, l.winCode,l.ctime from lottery_info l, user_info u, luckevent_info le where l.status=1 and l.winnerId=u.id and l.luckId=le.id order by l.ctime desc";
		List<Object[]> list = findBySql(firstIndex, pageSize, sql, null);
		if (list!=null && !list.isEmpty()) {
			result = new ArrayList<Winner>();
			Winner winner = null;
			for (Object[] obj : list) {
				winner = new Winner();
				winner.setUserId(String.valueOf(obj[0]));
				winner.setUsername(String.valueOf(obj[1]));
				winner.setLuckId(String.valueOf(obj[2]));
				winner.setTitle(String.valueOf(obj[3]));
				winner.setPicture(String.valueOf(obj[4]));
				winner.setCode(String.valueOf(obj[5]));
				winner.setCtime(sdf.format(obj[6]));
				
				result.add(winner);
			}
		}
		return result;
	}

	@Override
	public String phaseNum(String cgyId) throws Exception {
		String sql = "select count(1) from luckevent_info where cgyId=?";
		Object[] param = new Object[]{cgyId};
		List<BigInteger> list = findBySql(sql, param);
		if (list!=null && !list.isEmpty()) {
			return String.valueOf(list.get(0));
		}
		return "0";
	}

	@Override
	public List<LuckEvent> phaseList(String cgyId, int firstIndex, int pageSize)
			throws Exception {
		String sql = "select * from luckevent_info where cgyId=? order by ctime desc";
		Object[] param = new Object[]{cgyId};
		List<LuckEvent> list = findBySql(LuckEvent.class, firstIndex, pageSize, sql, param);
		return list;
	}

	@Override
	public List<LuckEvent> phaseList(int firstIndex, int pageSize)
			throws Exception {
		String sql = "select * from luckevent_info where status=2 order by offTime desc";
		List<LuckEvent> list = findBySql(LuckEvent.class, firstIndex, pageSize, sql, null);
		return list;
	}

	@Override
	public List<LuckEvent> list(int firstIndex, int pageSize) throws Exception {
		String sql = "select * from luckevent_info where status<>2 order by offTime desc, ctime desc";
		List<LuckEvent> list = findBySql(LuckEvent.class, firstIndex, pageSize, sql, null);
		return list;
	}
	
	@Override
	public List<LuckEventJoinUser> joinListById(String luckId, int firstIndex,
			int pageSize) throws Exception {
		List<LuckEventJoinUser> result = null;
		String sql = "select u.id,u.username, log.num,log.ctime,log.logId from exchangeluckevent_log log, user_info u where log.luckId=? and log.userId=u.id order by log.ctime desc";
		Object[] params = new Object[]{luckId};
		List<Object[]> list = findBySql(firstIndex, pageSize, sql, params);
		if (list!=null && !list.isEmpty()) {
			result = new ArrayList<LuckEventJoinUser>();
			LuckEventJoinUser luckEventJoinUser = null;
			for (Object[] obj : list) {
				luckEventJoinUser = new LuckEventJoinUser();
				luckEventJoinUser.setUserId(String.valueOf(obj[0]));
				luckEventJoinUser.setUsername(String.valueOf(obj[1]));
				luckEventJoinUser.setNumber(String.valueOf(obj[2]));
				luckEventJoinUser.setCodes(getCodes(String.valueOf(obj[4])));
				luckEventJoinUser.setCtime(sdf.format(obj[3]));
				
				result.add(luckEventJoinUser);
			}
		}
		return result;
	}
	/**
	 * 获取用户幸运码
	 * @param luckLogId
	 * @return
	 * @throws Exception
	 */
	private String[] getCodes(String luckLogId) throws Exception {
		String[] result = null;
		String sql = "select luckCode from luckeventcode_info where luckLogId=?";
		Object[] params = new Object[]{luckLogId};
		List<Object> list = findBySql(sql, params);
		if (list!=null && !list.isEmpty()) {
			result = new String[list.size()];
			for (int i=0; i<list.size(); i++) {
				result[i] = String.valueOf(list.get(i));
			}
		}
		return result;
	}

	@Override
	public List<LuckEventLog> find(String userId, String cgyId, int phase, int firstIndex,
			int pageSize) throws Exception {
		List<LuckEventLog> result = null;
		String sql = "select log.point,log.num,log.ctime from exchangeluckevent_log log, luckevent_info info where log.userId=? and info.cgyId=? and info.phase=? and log.luckId=info.id order by log.ctime desc";
		Object[] params = new Object[]{userId, cgyId, phase};
		List<Object[]> list = findBySql(firstIndex, pageSize, sql, params);
		if (list!=null && !list.isEmpty()) {
			result = new ArrayList<LuckEventLog>();
			LuckEventLog luckEventLog = null;
			for (Object[] obj : list) {
				luckEventLog = new LuckEventLog();
				luckEventLog.setPoint(String.valueOf(obj[0]));
				luckEventLog.setNumber(String.valueOf(obj[1]));
				luckEventLog.setCtime(sdf.format(obj[2]));
				
				result.add(luckEventLog);
			}
		}
		return result;
	}

	@Override
	public List<LuckEventLog> exchangeList(String userId, int firstIndex, int pageSize) throws Exception {
		List<LuckEventLog> result = null;
//		String sql = "select info.id,info.title,info.price,info.picture,info.depict,info.status,info.phase, log.point,log.num,log.ctime from exchangeluckevent_log log, luckevent_info info where log.userId=? and log.luckId=info.id order by log.ctime desc";
		String sql = "select info.id,info.title,info.price,info.picture,info.depict,info.status,info.phase,info.cgyId from (select log.luckId from exchangeluckevent_log log where log.userId=? group by log.luckId order by log.ctime desc) as ll, luckevent_info info where ll.luckId=info.id";
		Object[] params = new Object[]{userId};
		List<Object[]> list = findBySql(firstIndex, pageSize, sql, params);
		if (list!=null && !list.isEmpty()) {
			result = new ArrayList<LuckEventLog>();
			LuckEventLog luckEventLog = null;
			for (Object[] obj : list) {
				luckEventLog = new LuckEventLog();
				luckEventLog.setLuckId(String.valueOf(obj[0]));
				luckEventLog.setTitle(String.valueOf(obj[1]));
				luckEventLog.setPrice(String.valueOf(obj[2]));
				luckEventLog.setPicture(String.valueOf(obj[3]));
				luckEventLog.setDescribe(String.valueOf(obj[4]));
				luckEventLog.setStatus(String.valueOf(obj[5]));
				luckEventLog.setPhase(String.valueOf(obj[6]));
				luckEventLog.setCgyId(String.valueOf(obj[7]));
//				luckEventLog.setPoint(String.valueOf(obj[7]));
//				luckEventLog.setNumber(String.valueOf(obj[8]));
//				luckEventLog.setCtime(sdf.format(obj[9]));
				
				result.add(luckEventLog);
			}
		}
		return result;
	}
	
}
